Taeluf.com

Databasing.php

<?php

namespace Tlf\Tester;


/**
 * Convenience methods for database work
 */
trait Databasing {

    /**
     *  Create a pdo mysql instance by loading db settings from a file.
     *  File should contain `mysql.dbname`, `mysql.host`, `mysql.user`, and `mysql.password`
     *  @param $json_file_path a path to a json file
     *  @return a PDO instance
     */
    public function getPdoFromSettingsFile(string $json_file_path): \PDO {
        $settings = json_decode(file_get_contents($json_file_path),true);
        $pdo = new \PDO('mysql:dbname='.$settings['mysql.dbname'].';host='.$settings['mysql.host'],
            $settings['mysql.user'],$settings['mysql.password']);
        return $pdo;
    }

    public function getPdo($dbName = ':memory:'){
        $pdo = new \PDO('sqlite:'.$dbName);
        return $pdo;
    }


    /**
     * Perform a query and return the rows 
     *
     * @param $pdo a pdo instance
     * @param $sql an sql string
     * @param $where_cols values to bind to the sql string
     * @return an associative array of the first row
     */
    public function dbQuery(\PDO $pdo, string $sql, array $where_cols = []){
        $stmt = $pdo->prepare($sql);
        $stmt->execute($where_cols);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
    /**
     * Select a single row by id
     * @param $pdo a PDO instance
     * @param $table the table name
     * @param $id the id to query for 
     *
     * @return the row
     */
    public function dbSelectById(\PDO $pdo, string $table, int $id){
        $stmt = $pdo->prepare("SELECT * FROM `{$table}` WHERE id = {$id}");
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC)[0];
    }

    /**
     * DELETE a single row by id
     * @param $pdo a PDO instance
     * @param $table the table name
     * @param $id the id to query for 
     * @return num rows affected
     */
    public function dbDeleteById(\PDO $pdo, string $table, int $id){
        $stmt = $pdo->prepare("DELETE FROM `{$table}` WHERE id = {$id}");
        $stmt->execute();
        // return $stmt->fetchAll(\PDO::FETCH_ASSOC)[0];
        return $stmt->rowCount();
    }


    /**
     * Insert a row into the database
     * @param $pdo a PDO instance
     * @param $tableName a string table name
     * @param $values an array of key=>value pairs
     * @return The inserted row (queries DB by id for the row AFTER the insert)
     */
    public function dbInsert($pdo, $tableName, $values){

        $cols = [];
        $binds = [];
        foreach ($values as $key=>$value){
            $cols[] = $key;
            $binds[":{$key}"] = $value;
        }
        $colsStr = '`'.implode('`, `',$cols).'`';
        $bindsStr = implode(', ', array_keys($binds));
        $query = "INSERT INTO `${tableName}`(${colsStr}) 
                VALUES (${bindsStr})
            ";
        $stmt = $pdo->prepare($query);
        if ($stmt===false){
            echo "PDO ErrorInfo:\n";
            print_r($pdo->errorInfo());
            // echo "\n";
            // return;
            throw new \Exception("Could not insert values into databse.");
        } 
        $stmt->execute($binds);

        return $this->dbSelectById($pdo, $tableName, $pdo->lastInsertId());
    }

    public function dbInsertAll($pdo, $tableName, $rows){

        $cols = [];
        $binds = [];
        $query = '';
        foreach ($rows as $index=>$row){
            $new_bind_keys = [];
            foreach ($row as $key=>$value){
                $binds[":{$key}_$index"] = $value;
                $new_bind_keys[] = ":{$key}_$index";
            }
            $bindsStr = implode(', ', $new_bind_keys);
            if ($index!=0)$query .=",\n";
            $query .= "(${bindsStr})";
        }

        foreach (array_slice($rows,0,1)[0] as $key=>$value){
            $cols[] = $key;
        }
        $colsStr = '`'.implode('`, `',$cols).'`';
        $query = "INSERT INTO `${tableName}`(${colsStr}) 
                VALUES $query
            ";

        $stmt = $pdo->prepare($query);
        if ($stmt===false){
            echo "PDO ErrorInfo:\n";
            print_r($pdo->errorInfo());
            // echo "\n";
            // return;
            throw new \Exception("Could not insert rows into databse.");
        } 
        $stmt->execute($binds);

        return $this->dbSelectById($pdo, $tableName, $pdo->lastInsertId());
    }

    /**
     * @param array $cols array of columns like: `['col_name'=>'VARCHAR(80)', 'col_two'=> 'integer']`
     */
    public function createTable(\PDO $pdo, string $tableName, array $cols, bool $dropIfExists=false){
        $colStatements = [];
        foreach ($cols as $col => $definition){
            $statement = '`'.$col.'` '. $definition;
            $colStatements[] = $statement;
        }
        $colsSql = implode(", ", $colStatements);
        $drop = $dropIfExists ? "DROP TABLE IF EXISTS `{$tableName}`;\n" : '';
        $sql =
        <<<SQL
            {$drop}
            CREATE TABLE `{$tableName}`
            (
            {$colsSql}
            )
            ;
            
        SQL;

        $this->dbExec($pdo, $sql);
    }
    /**
     * Returns the first row from the query result.
     * @todo don't fetchAll(). Only fetch first row.
     */
    public function queryOne(\PDO $pdo, string $sql, ?array $binds=null): ?array{
        $rows = $this->query($pdo, $sql, $binds);
        return $rows[0] ?? null;
    }
    public function query(\PDO $pdo, string $sql, ?array $binds=null): array{
        $pdo = $pdo;
        $stmt = $pdo->prepare($sql);
        if ($stmt===false){
            var_dump($pdo->errorInfo());
            throw new \Exception("Sql problem.");
        }
        $stmt->execute($binds);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        return $rows;
    }

    public function dbExec($pdo, $sql, $binds=[]){
        $stmt = $pdo->prepare($sql);
        if ($stmt===false){
            var_dump($pdo->errorInfo());
            throw new \Exception("Sql problem.");
        }
        
        $stmt->execute($binds);
    }
}